//herron

import java.sql.*;
import java.util.Calendar;

public class sqlAPI {
        private static final String _JDBC = "com.mysql.jdbc.Driver";
        private static final String _DB = "jdbc:mysql://localhost/Library";
        
        private static final String _user = "root";
        private static final String _pass = "didyouseeit";
        
        private Connection cxn = null;
        
        public sqlAPI() {
                
                try {
                        Class.forName(_JDBC);
                        cxn = DriverManager.getConnection(_DB, _user, _pass);
                        /*
                        Statement stmt = null;
                        stmt = cxn.createStatement();
                        String query = "Select * from Users";
                        ResultSet result = stmt.executeQuery(query);
                        
                        while (result.next()) {
                        System.out.println(result.getString("username") + " " + 
                                        result.getString("password"));
                        }
                        
                        result.close();
                        stmt.close();
                        */

                } catch (Exception e){
                        e.printStackTrace();
                }
        }
        
        public void close() throws SQLException {
                cxn.close();
        }
        
        public boolean masher(String uname, String pword) {
                System.out.println("Mashed!");
                ResultSet cur = getAllBooks();
                try {
                while (cur.next()){
                        System.out.println("Book IDTitle:" + cur.getString("book_id") + cur.getString("title"));
                }
                } catch (Exception e) {
                        System.out.println("Masher Excepted.");
                        e.printStackTrace();
                }
                return true;
        }
        
        //Tested Working
        //Output: ResultSet of all books in table, or null if error
        public ResultSet getAllBooks() {
                PreparedStatement stmt = null;
                ResultSet result = null;
                try {
                        stmt = cxn.prepareStatement(
                                        "Select * from Books");
                        result = stmt.executeQuery();
                } catch (Exception e) {
                        System.out.println("Trouble getting Books *");
                        e.printStackTrace();
                }
                return result;
        }
        
        public ResultSet getAllUsers() {
            PreparedStatement stmt = null;
            ResultSet result = null;
            try {
                    stmt = cxn.prepareStatement(
                                    "Select * from Users");
                    result = stmt.executeQuery();
            } catch (Exception e) {
                    System.out.println("Trouble getting Users *");
                    e.printStackTrace();
            }
            return result;
    }
        
        public ResultSet getBook(int bookId) {
        	PreparedStatement stmt = null;
            ResultSet result = null;
            try {
                    stmt = cxn.prepareStatement(
                                    "Select * from Books where book_id=?");
                    stmt.setInt(1, bookId);
                    result = stmt.executeQuery();
            } catch (Exception e) {
                    System.out.println("Trouble getting Unique Book");
                    e.printStackTrace();
            }
            return result;
    }
        
        
        //Tested working - tries to authorize pw/username
        //Output - True if there is a matching user/pass in db, else false
        public int authUser(String uname, String pword) {
                
                //blank user/pass not allowed
                if (uname == null || pword == null) {
                        return -1;
                }
                
                //log
                System.out.println("Info login attempt: " + uname + " / " + pword); 
                
                //check that user is in db
                ResultSet rs = findUser(uname);
                String storedPw = null; 
                
                try {
                        while(rs.next()) {
                                storedPw = rs.getString("password");
                                if ( pword != null && pword.equals(storedPw) ) {
                                        return rs.getInt("user_id");
                                }
                        }
                        
                } catch (Exception e) {
                        System.out.println("Error: authUser (not just a badPW).");
                        e.printStackTrace();
                        return -1;
                }
                return -1;
        }
        
        public boolean isAdminUser(int uId) {
                PreparedStatement stmt = null;
                ResultSet rs = null;
                
                try {
                        stmt = cxn.prepareStatement("Select admin from Users where user_id = ?");
                        stmt.setInt(1, uId);
                        rs = stmt.executeQuery();
                        rs.next();
                        boolean ret = rs.getBoolean("admin");
                        return ret;
                } catch (SQLException e) {
                        System.out.println("isAdminUser() ain't sayin' nothin'");
                        e.printStackTrace();
                        return false;
                }
                
        }
        
        //Insert a user into the database
        public int addUser(String uname, String pword, 
                        String lastName, boolean admin) {
                if ( uname == null || pword == null || 
                                lastName == null) {
                        System.out.println("Error: addUser given null value");
                        return 1;
                }
                if (uname.length() > 254 || pword.length() > 254 || 
                               lastName.length() > 254) {
                        System.out.println("Error: addUser input too long ( > 254)");
                        return 1;
                }
                
                //if not null, then the userName exists
                if (findUser(uname) != null) {
                        return 2;
                }
                
                try {
                        PreparedStatement stmt = null;
                        
                        stmt = cxn.prepareStatement("Insert into Users "
                                        + "(username, password, last_name, admin) values"
                                        + " (?, ?, ? , ?)");
                        stmt.setString(1, uname);
                        stmt.setString(2, pword);
                        stmt.setString(3, lastName);
                        int iAdmin;
                        /*if (admin) {
                                iAdmin = 1;
                        } else {
                                iAdmin = 0;
                        }*/
                        stmt.setBoolean(4, admin);
                        
                        stmt.execute();
                        
                } catch (Exception e) {
                        System.out.println("Well, Sql.addUser didn't work.");
                        return 1;
                }
                return 0;
        }
        
        public boolean deleteUser(int userId) {
                PreparedStatement stmt = null;
                int result = -1;
                
                try {
                stmt = cxn.prepareStatement("delete from Users where user_id=?");
                stmt.setInt(1, userId);
                result = stmt.executeUpdate();
                } catch (Exception e) {
                        System.out.println("Couldn't deleteUser()");
                        e.printStackTrace();
                }
                if(result == 1) {
                        return true;
                } else if (result > 1){
                        System.out.println("I deleted more than 1 user from 1 ID... hmm");
                }
                return false;
        }
        
        
        //add a book into database
        public boolean addBook(String ISBN, String title, String authorLastName, 
                        String authorFirstName, String publisher, String pubDate,
                        String desc, String cover) {
                
                PreparedStatement stmt = null;
                
                /*if (title == null || authorLastName == null) {
                        System.out.println("Error: addBook given Null title "
                                        + "or author LastName");
                        return false;
                }*/
                
                if (ISBN == null) {     ISBN = ""; }
                if (authorFirstName == null) { authorFirstName ="";     }
                if (publisher == null) { publisher = ""; }
                if (pubDate == null) { pubDate = ""; }
                if (desc == null) { desc = ""; }
                if (cover == null) { cover = ""; }
                if (authorLastName == null) { authorLastName="";}
                
                  if (ISBN.length() > 254 || authorLastName.length() > 254 || 
                                authorFirstName.length() > 254 || publisher.length() > 254 ||
                                pubDate.length() > 254) {
                        System.out.println("Error: addBook Field too Long");
                }
                  
                  
                try {
                        stmt = cxn.prepareStatement("Insert into Books"
                                + " (ISBN, title, author_last_name, "
                                + "author_first_name, publisher, "
                                + "issue_date, description, cover, checkedOut) values "
                                + "(?, ?, ?, ?, ?, ?, ?, ?, ?)");
                
                        stmt.setString(1, ISBN);
                        stmt.setString(2, title);
                        stmt.setString(3, authorLastName);
                        stmt.setString(4, authorFirstName);
                        stmt.setString(5, publisher);
                        stmt.setString(6, pubDate);
                        stmt.setString(7, desc);
                        stmt.setString(8, cover);
                        stmt.setBoolean(9, false);
                        
                        stmt.execute();
                        
                } catch (Exception e) {
                        System.out.println("Error: addBook wouldn't go.");
                        e.printStackTrace();
                }
                return true;
        }
        
        public boolean deleteBook(int bookId) {
                PreparedStatement stmt = null;
                int result = -1;
                
                try {
                stmt = cxn.prepareStatement("delete from Books where book_id=?");
                stmt.setInt(1, bookId);
                result = stmt.executeUpdate();
                } catch (Exception e) {
                        System.out.println("Couldn't deleteBook()");
                        e.printStackTrace();
                }
                if(result == 1) {
                        return true;
                } else if (result > 1){
                        System.out.println("I deleted more than 1 book from 1 ID... hmm");
                }
                return false;
        }
        
        
        private ResultSet findUser(String uname) {
                
                ResultSet rs = null;
                
                if (uname == null) {
                        return rs;
                }
                
                try {
                        PreparedStatement stmt = cxn.prepareStatement(
                                        "Select * from Users where username = ?");
                        
                        stmt.setString(1, uname);
                        
                        rs = stmt.executeQuery();
                        
                } catch (Exception e) {
                        System.out.println("Error: Hmm... findUser... *Splat*");
                        e.printStackTrace();
                }
                return rs;
        }
        
        public int checkOut(int userId, int bookId) {
                PreparedStatement stmt = null;
                ResultSet result = null;
                 
                if (isBookOut(bookId)) {
                        return -1;
                }
                
                try {
                        stmt = cxn.prepareStatement(
                                        "Update Books set checkedOut=? where book_id=?");
                        stmt.setBoolean(1, true);
                        stmt.setInt(2, bookId);
                        stmt.executeUpdate();
                        //stmt.close();
                } catch (Exception e) {
                        System.out.println("Could Not update book as checkedOut");
                        e.printStackTrace();
                        return -2;
                }
                
                try {
                        stmt = cxn.prepareStatement(
                                        "Insert into CheckedOut (checkOut, UserID, BookID) values"
                                        + "(?, ?, ?)");
                        stmt.setTimestamp(1, getCurTimestamp());
                        stmt.setInt(2, userId);
                        stmt.setInt(3, bookId);
                        
                        stmt.executeUpdate();
                        
                        return 0;
                } catch (Exception e) {
                        System.out.println("Could not update checkedOut Table");
                        e.printStackTrace();
                        return -3;
                }
                
        }
        
        public int checkIn(int checkId) {
                PreparedStatement stmt = null;
                ResultSet result = null;

                try {
                        stmt = cxn.prepareStatement(
                                        "Select * from CheckedOut where ID=?");
                        stmt.setInt(1, checkId);
                        result = stmt.executeQuery();
                } catch (Exception e) {
                        System.out.println("Trouble getting checkedOut record");
                        e.printStackTrace();
                        return -2;
                }
                
                
                //trying to checkIn a book that's not out
                int bookId = -1;
                try {
                        bookId = result.getInt("BookId");
                } catch (Exception e) {
                        System.out.println("Couldn't update Books table with checkedin");
                        e.printStackTrace();
                }
                if (!isBookOut(bookId)) {
                        return -1;
                }
                
                //checkIn the book in Book DB
                try {
                        stmt = cxn.prepareStatement(
                                        "Update Books set checkedOut=? where book_id=?");
                        stmt.setBoolean(1, false);
                        stmt.setInt(2, bookId);
                        stmt.executeUpdate();
                        //stmt.close();
                } catch (Exception e) {
                        System.out.println("Could Not update book DB as checkedIn");
                        e.printStackTrace();
                        return -2;
                }
                
                //update the checkOut history db
                try {
                        stmt = cxn.prepareStatement(
                                        "Update CheckedOut set checkIn=? where ID=?");
                        stmt.setTimestamp(1, getCurTimestamp());
                        stmt.setInt(2, checkId);
                        
                        stmt.executeUpdate();
                        
                        return 0;
                } catch (Exception e) {
                        System.out.println("Couldn't update CheckedOut Table for checkin");
                        e.printStackTrace();
                        return -3;
                }
                
        }
        
        private Timestamp getCurTimestamp() {
                Calendar myCal = Calendar.getInstance();
                long curDate = myCal.getTime().getTime();
                Timestamp cTs = new Timestamp(curDate);
                return cTs;
        }
        
        public boolean isBookOut(int bookId) {
                PreparedStatement stmt = null;
                boolean isOut = false;
                
                try {
                        stmt = cxn.prepareStatement(
                                        "Select checkedOut from Books where book_id=?");
                        stmt.setInt(1, bookId);
                        
                        ResultSet result = stmt.executeQuery();
                        
                        while (result.next()) {
                                isOut = result.getBoolean("checkedOut");
                        }
                        
                        ///result.close();
                        //stmt.close();
                } catch (Exception e) {
                        System.out.println("isBookOut(): Problem");
                        e.printStackTrace();
                }
                return isOut;
        }
        
        public ResultSet getUserHistory(int userId) {
                PreparedStatement stmt = null;
                ResultSet result = null;
                //ArrayList<HashMap<String,Object>> listHist = new ArrayList<HashMap<String,Object>>();
                
                try {
                        stmt = cxn.prepareStatement(
                                "Select * from CheckedOut where UserID=userId");
                        result = stmt.executeQuery();
                        
                        /* Incase ResultSet is no fun
                        while(result.next()) {
                                HashMap<String,Object> cMap = new HashMap<String,Object>();
                                cMap.put("id", result.getObject("book_id"));
                                ...
                                listHist.add(cMap);
                        */
                        return result;
                } catch (Exception e) {
                        System.out.println("getUserHistory Failed");
                        e.printStackTrace();
                        return null;
                }
        }

        public ResultSet searchUser(int userId) {
                PreparedStatement stmt = null;
                try {
                        stmt = cxn.prepareStatement(
                                        "Select * from Users where user_id=?");
                        stmt.setInt(1, userId);
                        ResultSet result = stmt.executeQuery();
                        return result;
                } catch (Exception e) {
                        System.out.println("searchUser(int) problem");
                        e.printStackTrace();
                        return null;
                }
        }
        
        public ResultSet searchUser(String uname) {
                PreparedStatement stmt = null;
                try {
                        stmt = cxn.prepareStatement(
                                        "Select * from Users where (username like ? " + 
                                        " or first_name like ? or last_name like ?)");
                        stmt.setString(1, "%" + uname + "%");
                        stmt.setString(2, "%" + uname + "%");
                        stmt.setString(3, "%" + uname + "%");
                        ResultSet result = stmt.executeQuery();
                        return result;
                } catch (Exception e) {
                        System.out.println("searchUser(String) problem");
                        e.printStackTrace();
                        return null;
                }
        }
        
        public ResultSet searchAuthor(String authorName) {
                PreparedStatement stmt = null;
                try {
                        stmt = cxn.prepareStatement(
                                        "Select * from Books where (author_last_name like ? " +
                                        " or author_first_name like ?)");
                        stmt.setString(1, "%" + authorName + "%");
                        stmt.setString(2, "%" + authorName + "%");
                        ResultSet result = stmt.executeQuery();
                        return result;
                } catch (Exception e) {
                        System.out.println("searchBook by Author problem");
                        e.printStackTrace();
                        return null;
                }
        }
        
        public ResultSet searchTitle(String bookName) {
                PreparedStatement stmt = null;
                try {
                        stmt = cxn.prepareStatement(
                                        "Select * from Books where title like ?");
                        stmt.setString(1, "%" + bookName + "%");
                        ResultSet result = stmt.executeQuery();
                        return result;
                } catch (Exception e) {
                        System.out.println("searchBook by title problem");
                        e.printStackTrace();
                        return null;
                }
        }
        
        public int getNextId(String tableName) {
                /*String idColName = null;
                
                switch (tableName) {
                        case "Books":*/
        	int ret = -1;
                            try {
                                PreparedStatement stmt = cxn.prepareStatement(
                                                "Select * from Books order by book_id Desc");
                                ResultSet result = stmt.executeQuery();
                                result.next();
                                ret = (result.getInt("book_id"));
                                //result.close();
                                //stmt.close();
                                return ret;
                        } catch (Exception e) {
                                System.out.println("getNextString() Books failed");
                                e.printStackTrace();
                                return ret;
                        }/*
                        case "Users":
                            try {
                                PreparedStatement stmt = cxn.prepareStatement(
                                                "Select max(user_id) from Users");
                                ResultSet result = stmt.executeQuery();
                                ret = (result.getInt("user_id"));
                                //result.close();
                                //stmt.close();
                                return ret;
                        } catch (Exception e) {
                                System.out.println("getNextString() Users failed");
                                e.printStackTrace();
                                return ret;
                        }
                        case "CheckedOut":
                            try {
                                PreparedStatement stmt = cxn.prepareStatement(
                                                "Select max(ID) from CheckedOut");
                                ResultSet result = stmt.executeQuery();
                                ret = (result.getInt("ID"));
                                //result.close();
                                //stmt.close();
                                return ret;
                        } catch (Exception e) {
                                System.out.println("getNextString() CheckedOut failed");
                                e.printStackTrace();
                                return ret;
                        }
                        default:
                                break;
                }
                
                if (idColName == null) {
                        System.out.println("Table Name: " + tableName + " is not recognized.");
                } return ret;
                /*try {
                        PreparedStatement stmt = cxn.prepareStatement(
                                        "Select max(?) from ?");
                        stmt.setString(1, idColName);
                        stmt.setString(2, tableName);
                        ResultSet result = stmt.executeQuery();
                        ret = (1 + result.getInt(idColName));
                        result.close();
                        stmt.close();
                        return ret;
                } catch (Exception e) {
                        System.out.println("getNextString() failed");
                        e.printStackTrace();
                        return ret;
                }*/
        }
        
        public String getPath(int bookId, boolean isPdf) {
                String pathCol = null;
                String ret = null;
                if (isPdf) {
                        pathCol = "pdfPath";
                } else {
                        pathCol = "cover";
                }
                
                try {
                        PreparedStatement stmt = cxn.prepareStatement(
                                        "Select ? from Books where book_id=?");
                        ResultSet result = stmt.executeQuery();
                        result.next();
                        ret = result.getString(pathCol);
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                
                return ret;
                
                
        }

}
